<?php

class db {
		
		protected $msg=array(); //提示消息数组
		protected $tabName="";  //表名，自动获取
		protected $fieldList=array();  //表字段结构，自动获取
		//SQL的初使化
		protected $sql=array("field"=>"","where"=>"", "order"=>"", "limit"=>"", "group"=>"", "having"=>"");
		
		/**
		 * 用于重置成员属性
		 */
		protected function setNull(){
			$this->sql=array("field"=>"","where"=>"", "order"=>"", "limit"=>"", "group"=>"", "having"=>"");
		}
		/** 
		 * 用来获取表名
		 */
		public function __get($pro){
			if($pro=="tabName")
				return $this->tabName;
		}
		
		/**
		 *连贯操作调用field() where() order() limit() group() having()方法，组合SQL语句
		 */
		function __call($methodName, $args){
			$methodName=strtolower($methodName);
			if(array_key_exists($methodName, $this->sql)){
			
				if(($args[0] != 0 && empty($args[0])) || (is_string($args[0]) && trim($args[0])=='')){
					$this->sql[$methodName]="";
				}else{
					$this->sql[$methodName]=$args;
				}
				
			}else{
				echo " error __call";
				//Debug::addmsg("<font color='red'>调用类".get_class($this)."中的方法{$methodName}()不存在!</font>");
			}
			return $this;
		}
		
		
		/**
		 * 按指定的条件获取结果集中的记录数
		 */
	
		function total(){
			$where="";
			$data=array();
		
			$args=func_get_args();
			if(count($args)>0){
				$where = $this->comWhere($args);
				$data=$where["data"];
				$where= $where["where"];
			}else if($this->sql["where"] != ""){
				$where=$this->comWhere($this->sql["where"]);
				$data=$where["data"];
				$where=$where["where"];
				
			}
	
			$sql="SELECT COUNT(*) as count FROM {$this->tabName}{$where}";
			return $this->query($sql, __METHOD__,$data);			
		}
		
		/**
		 * 获取查询多条结果，返回二维数组
		 */
		function select(){
			
			$fields = $this->sql["field"] != "" ?  $this->sql["field"][0] :  implode(",", $this->fieldList);
		
			$where="";
			$data=array();
		
			$args=func_get_args();
			if(count($args)>0){
				$where = $this->comWhere($args);
				$data=$where["data"];
				$where= $where["where"];
			}else if($this->sql["where"] != ""){
				$where=$this->comWhere($this->sql["where"]);
				$data=$where["data"];
				$where=$where["where"];
				
			}
			
		
			$order = $this->sql["order"] != "" ?  " ORDER BY {$this->sql["order"][0]}" : " ORDER BY {$this->fieldList["pri"]} ASC";
			$limit = $this->sql["limit"] != "" ? $this->comLimit($this->sql["limit"]) : "";
			$group = $this->sql["group"] != "" ? " GROUP BY {$this->sql["group"][0]}" : "";
			$having = $this->sql["having"] != "" ? " HAVING {$this->sql["having"][0]}" : "";

		
			$sql="SELECT {$fields} FROM {$this->tabName}{$where}{$group}{$having}{$order}{$limit}";
			return $this->query($sql, __METHOD__,$data);	

		}
		
		/**
		 * 获取一条记录，返回一维数组
		 */
		function find($pri=""){
			$fields = $this->sql["field"] != "" ?  $this->sql["field"][0] :  implode(",", $this->fieldList);
		
			if($pri==""){
				$where= $this->comWhere($this->sql["where"]) ;
				$data=$where["data"];
				$where = $this->sql["where"] != "" ? $where["where"] : "";		

			}else{
				$where=" where {$this->fieldList["pri"]}=?";  
				$data[]=$pri;
			}
			$order = $this->sql["order"] != "" ?  " ORDER BY {$this->sql["order"][0]}" : "";
			$sql="SELECT {$fields} FROM {$this->tabName}{$where}{$order} LIMIT 1";



  			return $this->query($sql,__METHOD__,$data);
		
		}
		
		//filter = 1 去除 " ' 和 HTML 实体， 0则不变
		private function check($array, $filter){
			$arr=array();
		
			foreach($array as $key=>$value){
				$key=strtolower($key);
				if(in_array($key, $this->fieldList)){
					if(is_array($filter) && !empty($filter)){
						if(in_array($key, $filter)){
							$arr[$key]=$value;	
						}else{
							$arr[$key]=stripslashes(htmlspecialchars($value));
						}
					}else if(!$filter) {
						$arr[$key]=$value;
					}else{
						$arr[$key]=stripslashes(htmlspecialchars($value));
					}
				}	
			}
			return $arr;
		}
		
		/**
		 * 向数据库中插入一条记录
		 */
		function insert($array=null, $filter=1){
			if(is_null($array))
				$array=$_POST;

			$array=$this->check($array, $filter);

			$sql = "INSERT INTO {$this->tabName}(".implode(',', array_keys($array)).") VALUES (".implode(',', array_fill(0, count($array), '?')) . ")";

			return $this->query($sql,__METHOD__,array_values($array));
			
		}
		
		/**
		 * 更新数据表中指定条件的记录
		 */
		function update($array=null, $filter=1){
                
			if(is_null($array))
			$array=$_POST; 

			$data=array();
			if(is_array($array)){
			
				if(array_key_exists($this->fieldList["pri"], $array)){
					$pri_value=$array[$this->fieldList["pri"]];
					unset($array[$this->fieldList["pri"]]);	
				}

				$array=$this->check($array, $filter); 
				$s = '';
				foreach ($array as $k=>$v) {

					$s .="{$k}=?,";
					$data[]=$v;  //value
				}
				$s=rtrim($s, ",");
				$setfield=$s;
			}else{
				$setfield=$array;
				$pri_value='';
			
			}

		
			$order = $this->sql["order"] != "" ?  " ORDER BY {$this->sql["order"][0]}" : "";
			$limit = $this->sql["limit"] != "" ? $this->comLimit($this->sql["limit"]) : "";

			if($this->sql["where"] != ""){
				$where=$this->comWhere($this->sql["where"]);
				$sql="UPDATE  {$this->tabName} SET {$setfield}".$where["where"];
				
				if(!empty($where["data"])) {
					foreach($where["data"] as $v){
						$data[]=$v; //value
					}
				}
				$sql.=$order.$limit;
			}else{
			
				$sql="UPDATE {$this->tabName} SET {$setfield}  WHERE {$this->fieldList["pri"]}=?";
				$data[]=$pri_value; //value
			}

			return $this->query($sql,__METHOD__,$data);	
		
		}
		
		/**
		 * 删除满足条件的记录		 
		 */
		function delete(){
			$where="";
			$data=array();
			
			$args=func_get_args();
			if(count($args)>0){
				$where = $this->comWhere($args);
				$data=$where["data"];
				$where= $where["where"];
			}else if($this->sql["where"] != ""){
				$where=$this->comWhere($this->sql["where"]);
				$data=$where["data"];
				$where=$where["where"];
				
			}

			$order = $this->sql["order"] != "" ?  " ORDER BY {$this->sql["order"][0]}" : "";
			$limit = $this->sql["limit"] != "" ? $this->comLimit($this->sql["limit"]) : "";
			
			if($where=="" && $limit==""){
				$where=" where {$this->fieldList["pri"]}=''";
			}
			

			$sql="DELETE FROM {$this->tabName}{$where}{$order}{$limit}";
		
			return $this->query($sql, __METHOD__,$data);
		}
		
		private function comLimit($args){
			if(count($args)==2){
				return " LIMIT {$args[0]},{$args[1]}";
			}else if(count($args)==1){
				return " LIMIT {$args[0]}";
			}else{
				return '';
			}	
		}
		
		/**
		 * 用来组合SQL语句中的where条件 
		 */ 
		private function comWhere($args){
			$where=" WHERE ";
			$data=array();
			
			if(empty($args))
				return array("where"=>"", "data"=>$data);
	
			foreach($args as $option) {
				if(empty($option)){
					$where = ''; //条件为空，返回空字符串；如'',0,false 返回： '' //5
					continue;
				}else if(is_string($option)){
			       	 	if (is_numeric($option[0])) {
						$option = explode(',', $option); //3
						$where .= "{$this->fieldList["pri"]} IN(" . implode(',', array_fill(0, count($option), '?')) . ")";
						$data=$option;
						continue;
					} else {
						$where .= $option; //2
						continue;
					}	
				}else if(is_numeric($option)){
					$where .="{$this->fieldList["pri"]}=?";   //1
					$data[0]=$option;
					continue;
				}else if(is_array($option)){
					if (isset($option[0])) {
           			 		//如果是1维数组，array(1,2,3,4);  //4
						$where .= "{$this->fieldList["pri"]} IN(" . implode(',', array_fill(0, count($option), '?')) . ")";
						$data=$option;
						continue;
        				}
					
					
					foreach($option as $k => $v ){
          					if (is_array($v)) {
                					// 5、如果是2维数组，array('id'=>array(1,2,3,4))
							$where .= "{$k} IN(" . implode(',', array_fill(0, count($v), '?')) . ")";					
							foreach($v as $val){
								$data[]=$val;
							}
           					 } else if (strpos($k, ' ')) {
               						 // 6、array('add_time >'=>'2010-10-1')，条件key中带 > < 符号
							 $where .= "{$k}?";
							 $data[]=$v;
           					 } else if (isset($v[0]) && $v[0] == '%' && substr($v, -1) == '%') {
               						 // 7、array('name'=>'%中%')，LIKE操作
							 $where .= "{$k} LIKE ?";
							 $data[]=$v;
               					} else {
                					// 8、array('res_type'=>1)
							$where .= "{$k}=?";
							$data[]=$v;
                				}
						$where.=" AND ";
					}
				
					$where =rtrim($where, "AND ");
					$where.=" OR ";
					continue;
				}
			}
			$where=rtrim($where, "OR ");
			return array("where"=>$where, "data"=>$data);
		}
		
		protected function escape_string_array($array){
			if(empty($array))
				return array();
				
		 	$value=array();
				foreach($array as $val){
				$value[]=str_replace(array('"', "'"), '', $val);
			}
		 	return $value;
		}

		/**
		 * 输出完整的SQL 语句，用于调试
		 * sql to msg ，得到sql语句，然后通过$this->setmsg 放起来，暂时这样处理
		*/
		protected function stom($sql, $params_arr){
		 	     
			if (false === strpos($sql, '?') || count($params_arr) == 0) return $sql;

			// 进行 ? 的替换，变量替换
			if (false === strpos($sql, '%')) {
				
				// 不存在%，替换问号为s%，进行字符串格式化
				$sql = str_replace('?', "'%s'", $sql);
				array_unshift($params_arr, $sql);
				return call_user_func_array('sprintf', $params_arr); //调用函数和所用参数
				
			}
		}

}

class dbtest extends db {
		//static private $db_instance = NULL;
		public $pdo=null;
		public $qz = null;
		
		/**
		 *获取数据库连接对象PDO
		*/
		
		
		function __construct($dbconf){

			try{
			
				$dsn="mysql:host=".$dbconf["host"].";dbname=".$dbconf["dbname"].";charset=".$dbconf["charset"];

				$pdo=new PDO($dsn, $dbconf["user"], $dbconf["pass"], array(PDO::ATTR_PERSISTENT=>true));
				$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
				$this->pdo=$pdo;
				
				$pdo->exec('SET CHARACTER SET utf8');//dsn里的暂时不管用，还得声明一下，以后解决
				$this->qz = $dbconf["qz"];
				//return $pdo;
			}catch(PDOException $e){
				echo "连接数库失败：".$e->getMessage();
			}
		}
		
		/*
		
		*/
		
		function init() {
		
			
		
		}
		
		/*
		function setdb($dbname) {
		
			try{
				$this->pdo->exec('use '.$dbname);
			}catch(PDOException $e) {
				echo "切换数据库失败 ".$dbname;
				exit();
			}
		
			return $this;
		}
		*/
		
		function setqz($qz) {
		
			$this->qz = $qz;
			return $this;
		
		}
		
		/**
		 * 自动获取表结构
		 */ 
		function settable($tabName){
			//$cachefile=PROJECT_PATH."runtime/data/".$tabName.".php";
			$this->tabName=$this->qz.$tabName; //加前缀的表名
		
			//if(!file_exists($cachefile)){
			
				try{
					$stmt=$this->pdo->prepare("desc {$this->tabName}");
					$stmt->execute();
					$fields=array();
					while($row=$stmt->fetch(PDO::FETCH_ASSOC)){
						if($row["Key"]=="PRI"){
							$fields["pri"]=strtolower($row["Field"]);
						}else{
							$fields[]=strtolower($row["Field"]);
						}
					}
					//如果表中没有主键，则将第一列当作主键
					if(!array_key_exists("pri", $fields)){
						$fields["pri"]=array_shift($fields);		
					}
					//if(!DEBUG)
						//file_put_contents($cachefile, "<?php ".json_encode($fields));
					$this->fieldList=$fields;
				}catch(PDOException $e){
					echo 'settable error ';
					//Debug::addmsg("<font color='red'>异常：".$e->getMessage().'</font>');
					exit();
				}
				
			//}else{
				//$json=ltrim(file_get_contents($cachefile),"<?ph ");
				//$this->fieldList=(array)json_decode($json, true);	
			//}
			//Debug::addmsg("表<b>{$this->tabName}</b>结构：".implode(",", $this->fieldList),2); //debug
			return $this;
			
		}
		
		/**
		 * 执行SQL语句的方法
		 * @param	string	$sql		用户查询的SQL语句
		 * @param	string	$method		SQL语句的类型（select,find,total,insert,update,other）
		 * @param	array	$data		为prepare方法中的?参数绑定值
		 * @return	mixed			根据不同的SQL语句返回值
		 */
		function query($sql, $method,$data=array()){
			
			$this->setNull();//初始化sql
			
			$value = $this->escape_string_array($data);
			$marr = explode("::",$method);
			$method=strtolower(array_pop($marr));
			
			if(strtolower($method) == trim("total")) {
				$sql=preg_replace('/select.*?from/i','SELECT count(*) as count FROM',$sql);
			}
			
			
			$memkey=$this->stom($sql,$value);
			$this->setMsg($memkey);
			
			try{
				$stmt = $this->pdo->prepare($sql);
				$result = $stmt->execute($value);
				
				switch($method) {
				
					case "select":
						$data = $stmt->fetchall(PDO::FETCH_ASSOC);
						return $data;
						break;
					
					case "find":
						$data = $stmt->fetch(PDO::FETCH_ASSOC);
						return $data;
						break;
					
					case "total":
						$row = $stmt->fetch(PDO::FETCH_NUM);
						return $row[0];
						break;
						
					case "insert":
						return $this->pdo->lastInsertId();
						break;
					
					case "delete":
					case "update":
						return $stmt->rowCount();
						break;
					default:
						return $result;
						
					
				
				};
				
			}catch(PDOException $e){
				
				//Debug::addmsg("<font color='red'>SQL error: ".$e->getMessage().'</font>');
				//Debug::addmsg("请查看：<font color='#005500'>".$memkey.'</font>'); //debug
			}	
		}
		
		/*
		 * 数据库的版本
		 * @return	string		返回数据库系统的版本
		 */
		function dbVersion() {
			return $this->pdo->getAttribute(PDO::ATTR_SERVER_VERSION);
		}
		 /**
		  * 设置提示信息
		  * @param	mixed	$mess	提示消息字符串或数组
		  */
		 function setMsg($mess){
			 if(is_array($mess)){
				 foreach($mess as $one){
					 $this->msg[]=$one;
				 }
			 }else{
			 	$this->msg[]=$mess;
			 }
		 
		 }
		 /**
		  * 获取提示信息
		  * @return	string	提示消息字符串
		  */
		 function getMsg(){
		 	$str='';

			foreach($this->msg as $msg){
				$str.=$msg.'<br>';
			}
			return $str;
		 }

}